﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.IO;
using SES.CMS.BL;
using SES.CMS.DO;

namespace SES.CMS.AdminCP
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }
        protected void btnImport_Click(object sender, EventArgs e)
        {
            string ExcelFilename = FileUpload1.PostedFile.FileName;
            string ExcelFileNameonly = ExcelFilename.Substring(ExcelFilename.LastIndexOf("\\") + 1);
            string FileExt = ExcelFilename.Substring(ExcelFilename.LastIndexOf(".") + 1);
            string Filenamewithoutextn = ExcelFileNameonly.Remove(ExcelFileNameonly.LastIndexOf("."));
            int i = 0;
            if (FileExt.ToLower() == "xls")
            {
                FileUpload1.PostedFile.SaveAs(Server.MapPath("/Imported Files/") + Filenamewithoutextn + ".xls");
                string strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("/Imported Files/") + Filenamewithoutextn + ".xls;Extended Properties=Excel 8.0;";
                OleDbConnection olecon = new OleDbConnection(strConnectionString);
                int RecordCount = 0;
                olecon.Open();
                OleDbCommand cmd = olecon.CreateCommand();
                cmd.CommandText = "SELECT * FROM [Sheet1$]";
                DataSet ds = new DataSet();
                OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                OleDbDataReader dr = cmd.ExecuteReader();
                try
                {
                    int loai = 0;
                    
                    while (dr.Read())
                    {
                        string dfdfdf = dr[0].ToString();
                        if (dr[0].ToString() != "")
                        {
                            cmsSanPhamDO obj = new cmsSanPhamDO();
                            if (dr[1].ToString() != "")
                                obj = new cmsSanPhamBL().SelectByMaSP(dr[1].ToString());
                            
                            if (dr[2].ToString() != "")
                                obj.TenSanPham = dr[2].ToString();
                              obj.Mota = "";
                            if (dr[3].ToString() != "")
                            {
                                cmsNhanHieuDO objNH = new cmsNhanHieuBL().SelectByTen(dr[3].ToString());
                                if (objNH.NhanHieuID > 0)
                                      obj.NhanHieuID = objNH.NhanHieuID;
                                else
                                {
                                    objNH.TenNhanHieu = dr[3].ToString();
                                    obj.NhanHieuID = new cmsNhanHieuBL().Insert(objNH);
                                }
                            }
                            if (dr[4].ToString() != "")
                            {
                                cmsDonViDO objDV = new cmsDonViBL().SelectByTen(dr[4].ToString());
                                if (objDV.DonViID > 0)
                                    obj.DonViID = objDV.DonViID;
                                else
                                {
                                    objDV.DonViTinh = dr[4].ToString();
                                    obj.DonViID = new cmsDonViBL().Insert(objDV);
                                }
                            }
                            if ((dr[5].ToString() != "")&&(dr[5].ToString()!="0"))
                                obj.DonGia = Int64.Parse(dr[5].ToString().Replace(".", ""));
                            obj.LoaiSanPhamID = loai;
                            if (obj.SanPhamID == 0)
                            {
                                new cmsSanPhamBL().Insert(obj);
                            }
                            else
                            {
                                new cmsSanPhamBL().Update(obj);
                            }
                            RecordCount++;
                            i++;
                        }
                        else
                        {
                            cmsLoaiSanPhamDO obj1 = new cmsLoaiSanPhamDO();
                            obj1 = new cmsLoaiSanPhamBL().SelectByTen(dr[2].ToString());
                            if (obj1.LoaiSanPhamID > 0)
                                loai = obj1.LoaiSanPhamID;
                            else
                            {
                                obj1.TenLoaiSanPham = dr[2].ToString();
                                obj1.MoTa = dr[1].ToString();
                                loai = new cmsLoaiSanPhamBL().Insert(obj1);
                            }
                        }
                    }
                    Functions.Alert("Thong cong" + RecordCount);
                    
                    dr.Close();
                    olecon.Close();
                }
                catch (Exception ex)
                {
                    int fsdf = i;
                    lblLoi.Text = ex.ToString() + " sai o dong: " + i + " " + dr[2].ToString() ;
                }
            }

        }
    }
}
